--[[
sqltb 是用sql表来存储一类节点
使用sqltb的dpl过滤器, 过滤一类节点(某一层节点)
sqltb需要的配置参数:
	 类型:表名 映射配置
	对于每一个映射
	{
		node=表名
		pre=path前缀,
		single=是否单层,
		id=对应列名,
		pid=对应列名,
		ik=,ts=对应列名,
		kfmap=列名映射,
		pto=缺省原型id,
	}
--]]
local tonumber,tostring,assert,select=
	  tonumber,tostring,assert,select
local os_time=
	  os.time
local string_match,string_format=
	  string.match,string.format
require'zdc.idutil'
local PreRemove=zdc.idutil.PreRemove
local IsRoot=zdc.idutil.IsRoot
module(..., package.seeall)

local dbcfun={
	id='id',
	pid='pid',
	ik='ik',
	ts='ts',
	AND=function(...)
		local cnt=select('#',...)
		local s='(' .. select(1,...)
		for i=2,cnt do
			s=s .. ' and ' .. select(i,...)
		end
		s=s..')'
		return s
	end,
	OR=function(...)
		local cnt=select('#',...)
		local s='(' .. select(1,...)
		for i=2,cnt do
			s=s .. ' or ' .. select(i,...)
		end
		s=s..')'
		return s
	end,
	GT=function(k,a)
		return k .. '>'..a
	end,
	LT=function(k,a)
		return k .. '<'..a
	end,
	GE=function(k,a)
		return k .. '>='..a
	end,
	LE=function(k,a)
		return k .. '<='..a
	end,
	EQ=function(k,a)
		return k .. '=='..a
	end,
}
local _wheremeta= {__index=dbcfun}
function _getdbwherefun(where,whereenv)
	if not where or #where==0 then return end
	local f=assert(loadstring('return '..where))
	setfenv(f,whereenv)
	return f()
end

local sqltb = {}
local sqltb_mt = {__metatable = {},__index=sqltb}

function MakeInhMT(ch)
	ch=ch or {}
	for _i,_j in pairs(dbutil) do
		ch[_i]=_j
	end
	return ch
end

local schemas = {}
schemas.sqlex_limit="LIMIT %%s"

local function build_db_cf(tablenode,tbconf)
	local cf={}
	cf.pre=tbconf.pre
	cf.pre_=tbconf.pre..'/'
	cf.schemas=tbconf.schemas
	cf.node=tablenode
	cf.id=tbconf.id
	cf.ik=tbconf.ik
	cf.ts=tbconf.ts
	cf.pid=tbconf.pid
	cf.pto=tbconf.pto
	cf.kfmap=tbconf.kfmap --k={f=?,type}
	local flst,fmap={},{}
	
	if cf.ik then table.insert(flst,cf.ik) end
	if cf.ts then table.insert(flst,cf.ts) end
	
	if tbconf.nomap then
		cf.nomap=tbconf.nomap
		for k,v in pairs(cf.kfmap) do
			table.insert(flst,k)
		end
	else
		for k,v in pairs(cf.kfmap) do
			fmap[v.f]=k
			table.insert(flst,v.f)
		end
	end
	cf.qfl=table.concat(flst,',')
	if cf.pid then table.insert(flst,1,cf.pid) end
	cf.sfl=table.concat(flst,',')
	cf.flst=flst
	if tbconf.nomap then
		cf.nomap=tbconf.nomap
	else
		cf.fmap=fmap
	end
	if cf.pid then
		cf.w_pid=string_format("WHERE %s = %%s",cf.pid)
		cf.w_x_pid=string_format("WHERE %s = %%%%s and ",cf.pid)
		cf.x_pid=string_format("WHERE %s = %%%%s ",cf.pid)
		
		cf.w_preid=string_format("WHERE %s LIKE %%s",cf.id)
		cf.w_x_preid=string_format("WHERE %s LIKE %%%%s and ",cf.pid)
		cf.x_preid=string_format("WHERE %s LIKE %%%%s ",cf.pid)
	else
		cf.w_pid=''
		cf.w_x_pid='WHERE '
		cf.x_pid=''

		cf.w_preid=''
		cf.w_x_preid='WHERE '
		cf.x_preid=''
	end
	return cf
end

function new(db,tablenode,tbconf)
	local base=db
	local subs=base._sub or {}
	if subs[tablenode] then return subs[tablenode] end
	local obj={schemas=schemas,tablenode=tablenode,dbc=base}
	obj.cf=build_db_cf(tablenode,tbconf)
	setmetatable(obj, sqltb_mt)
	obj._whereenv=setmetatable({
			_esc=function(v) return base:prepare_val(v) end,
			_col=obj.cf.fmap and function(k) return obj.cf.fmap[k] end or function(k) return k end
		},_wheremeta)
	obj:init()
	subs[tablenode]=obj
	base._sub=subs
	return obj 
end

function sqltb:deflate_node(a,b,c)
	return a
end

function sqltb:init()
	self:_init_query()
end

local function comma(a)
	return (a and a ..',') or ''
end

function sqltb:_init_query()
	local dbc=self.dbc
	local obj=self

	-- Create the two data tables, if they don't already exist
	local cf=self.cf
	local cmd=dbc:prepare(cf.schemas.init:format(cf.node))
	assert(dbc:execute(cmd))

	--upgrade table col
	do
		local cur = assert(dbc:execute(string_format("SELECT * from %s WHERE 1=2",cf.node)))
		local cols=cur:getcolnames()
		local colmap={}
		for i,v in ipairs(cols) do
			colmap[v]=i
		end
		if obj.cf.nomap then
			for k,v in pairs(obj.cf.kfmap) do
				if not colmap[k] then
					assert(dbc:execute(string_format("ALTER TABLE %s ADD %s %s",obj.cf.node,k,dbc.fmt_type(v.t))))
				end
			end
		elseif obj.cf.fmap then
			for k,v in pairs(obj.cf.kfmap) do
				if not colmap[v.f] then
					assert(dbc:execute(string_format("ALTER TABLE %s ADD %s %s",obj.cf.node,v.f,dbc.fmt_type(v.t))))
				end
			end
		else
			error('config error for sqltb need nomap or fmap')
		end
	end
	--
	
	-- Pre-build our queries
	obj.queries = {
		GET_NODE_ = string_format("SELECT %s,%s from %s WHERE %s = %%s;",cf.id,cf.qfl,cf.node,cf.id),
		GET_NODES = string_format("SELECT %s,%s FROM %s;", cf.id,cf.qfl,cf.node),
		GET_IDS = string_format("SELECT %s FROM %s;", cf.id,cf.node),
		GET_CNT = string_format("SELECT count(*) FROM %s;", cf.node),

		GET_CHILDNODES=string_format("SELECT %s,%s from %s %s;",cf.id,cf.qfl,cf.node,cf.w_pid),
		GET_CHILDNODES_w_s_l=string_format("SELECT %s,%s from %s %s %%s ORDER BY %%s %s;", cf.id,cf.qfl,cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDNODES_w_l=string_format("SELECT %s,%s from %s %s %%s %s;",cf.id,cf.qfl,cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDNODES_w_s=string_format("SELECT %s,%s from %s %s %%s ORDER BY %%s;",cf.id,cf.qfl,cf.node,cf.w_x_pid),
		GET_CHILDNODES_w=string_format("SELECT %s,%s from %s %s %%s;",cf.id,cf.qfl,cf.node,cf.w_x_pid),
		GET_CHILDNODES_s_l=string_format("SELECT %s,%s from %s %s ORDER BY %%s %s;",cf.id,cf.qfl,cf.node,cf.x_pid,schemas.sqlex_limit),
		GET_CHILDNODES_s=string_format("SELECT %s,%s from %s %s ORDER BY %%s;",cf.id,cf.qfl,cf.node,cf.x_pid),
		GET_CHILDNODES_l=string_format("SELECT %s,%s from %s %s %s;",cf.id,cf.qfl,cf.node,cf.x_pid,schemas.sqlex_limit),

		GET_PREFIXNODES=string_format("SELECT %s,%s from %s %s;",cf.id,cf.qfl,cf.node,cf.w_preid),
		GET_PREFIXNODES_w_s_l=string_format("SELECT %s,%s from %s %s %%s ORDER BY %%s %s;", cf.id,cf.qfl,cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXNODES_w_l=string_format("SELECT %s,%s from %s %s %%s %s;",cf.id,cf.qfl,cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXNODES_w_s=string_format("SELECT %s,%s from %s %s %%s ORDER BY %%s;",cf.id,cf.qfl,cf.node,cf.w_x_preid),
		GET_PREFIXNODES_w=string_format("SELECT %s,%s from %s %s %%s;",cf.id,cf.qfl,cf.node,cf.w_x_preid),
		GET_PREFIXNODES_s_l=string_format("SELECT %s,%s from %s %s ORDER BY %%s %s;",cf.id,cf.qfl,cf.node,cf.x_preid,schemas.sqlex_limit),
		GET_PREFIXNODES_s=string_format("SELECT %s,%s from %s %s ORDER BY %%s;",cf.id,cf.qfl,cf.node,cf.x_preid),
		GET_PREFIXNODES_l=string_format("SELECT %s,%s from %s %s %s;",cf.id,cf.qfl,cf.node,cf.x_preid,schemas.sqlex_limit),

		GET_CHILDIDS=string_format("SELECT %s from %s %s;",cf.id,cf.node,cf.w_pid),
		GET_CHILDIDS_w_s_l=string_format("SELECT %s from %s %s %%s ORDER BY %%s %s;", cf.id,cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDIDS_w_l=string_format("SELECT %s from %s %s %%s %s;",cf.id,cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDIDS_w_s=string_format("SELECT %s from %s %s %%s ORDER BY %%s;",cf.id,cf.node,cf.w_x_pid),
		GET_CHILDIDS_w=string_format("SELECT %s from %s %s %%s;",cf.id,cf.node,cf.w_x_pid),
		GET_CHILDIDS_s_l=string_format("SELECT %s from %s %s ORDER BY %%s %s;",cf.id,cf.node,cf.x_pid,schemas.sqlex_limit),
		GET_CHILDIDS_s=string_format("SELECT %s from %s %s ORDER BY %%s;",cf.id,cf.node,cf.x_pid),
		GET_CHILDIDS_l=string_format("SELECT %s from %s %s %s;",cf.id,cf.node,cf.x_pid,schemas.sqlex_limit),

		GET_PREFIXIDS=string_format("SELECT %s from %s %s;",cf.id,cf.node,cf.w_preid),
		GET_PREFIXIDS_w_s_l=string_format("SELECT %s from %s %s %%s ORDER BY %%s %s;", cf.id,cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXIDS_w_l=string_format("SELECT %s from %s %s %%s %s;",cf.id,cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXIDS_w_s=string_format("SELECT %s from %s %s %%s ORDER BY %%s;",cf.id,cf.node,cf.w_x_preid),
		GET_PREFIXIDS_w=string_format("SELECT %s from %s %s %%s;",cf.id,cf.node,cf.w_x_preid),
		GET_PREFIXIDS_s_l=string_format("SELECT %s from %s %s ORDER BY %%s %s;",cf.id,cf.node,cf.x_preid,schemas.sqlex_limit),
		GET_PREFIXIDS_s=string_format("SELECT %s from %s %s ORDER BY %%s;",cf.id,cf.node,cf.x_preid),
		GET_PREFIXIDS_l=string_format("SELECT %s from %s %s %s;",cf.id,cf.node,cf.x_preid,schemas.sqlex_limit),

		GET_CHILDCNT=string_format("SELECT count(*) from %s %s;",cf.node,cf.w_pid),
		GET_CHILDCNT_w_s_l=string_format("SELECT count(*) from %s %s %%s ORDER BY %%s %s;",cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDCNT_w_l=string_format("SELECT count(*) from %s %s %%s %s;",cf.node,cf.w_x_pid,schemas.sqlex_limit),
		GET_CHILDCNT_w_s=string_format("SELECT count(*) from %s %s %%s ORDER BY %%s;",cf.node,cf.w_x_pid),
		GET_CHILDCNT_w=string_format("SELECT count(*) from %s %s %%s;",cf.node,cf.w_x_pid),
		GET_CHILDCNT_s_l=string_format("SELECT count(*) from %s %s ORDER BY %%s %s;",cf.node,cf.x_pid,schemas.sqlex_limit),
		GET_CHILDCNT_s=string_format("SELECT count(*) from %s %s ORDER BY %%s;",cf.node,cf.x_pid),
		GET_CHILDCNT_l=string_format("SELECT count(*) from %s %s %s;",cf.node,cf.x_pid,schemas.sqlex_limit),

		GET_PREFIXCNT=string_format("SELECT count(*) from %s %s;",cf.node,cf.w_preid),
		GET_PREFIXCNT_w_s_l=string_format("SELECT count(*) from %s %s %%s ORDER BY %%s %s;",cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXCNT_w_l=string_format("SELECT count(*) from %s %s %%s %s;",cf.node,cf.w_x_preid,schemas.sqlex_limit),
		GET_PREFIXCNT_w_s=string_format("SELECT count(*) from %s %s %%s ORDER BY %%s;",cf.node,cf.w_x_preid),
		GET_PREFIXCNT_w=string_format("SELECT count(*) from %s %s %%s;",cf.node,cf.w_x_preid),
		GET_PREFIXCNT_s_l=string_format("SELECT count(*) from %s %s ORDER BY %%s %s;",cf.node,cf.x_preid,schemas.sqlex_limit),
		GET_PREFIXCNT_s=string_format("SELECT count(*) from %s %s ORDER BY %%s;",cf.node,cf.x_preid),
		GET_PREFIXCNT_l=string_format("SELECT count(*) from %s %s %s;",cf.node,cf.x_preid,schemas.sqlex_limit),

		NODE_EXISTS = string_format("SELECT DISTINCT %s FROM %s WHERE %s = %%s;",cf.id,cf.node,cf.id),
		INSERT_NODE = string_format("REPLACE INTO %s (%s,%%s) VALUES (%%s,%%s);",cf.node,cf.id),
		REMOVE_NODE = string_format("DELETE from %s WHERE %s = %%s", cf.node,cf.id),
		REMOVE_SUBNODE = string_format("DELETE FROM %s WHERE %s LIKE %%s;",cf.node,cf.id),
	}
	return
end

local function maptoraw(cf,row)
	local flst,fmap,n,d=cf.flst,cf.fmap,{},{}
	if cf.ik then d.ik=tonumber(row[cf.ik]) end
	if cf.ts then d.ts=tonumber(row[cf.ts]) end
	if fmap then
		for i=1,#flst do
			local f=flst[i]
			n[fmap[f]]=row[f]
		end
	else
		for i=1,#flst do
			local f=flst[i]
			n[f]=row[f]
		end
	end
	if cf.pto then n._pto=cf.pto end
	d.data=n
	return d
end

function sqltb:node_exists(id)
	local dbc,cf=self.dbc,self.cf
	local id=PreRemove(id,cf.pre_)
	assert(id)

	local cmd = dbc:prepare(self.queries.NODE_EXISTS, id)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "*a")
	cur:close()

	return row ~= nil
end

function sqltb:get_node(id)
	local dbc,cf=self.dbc,self.cf
	local id=PreRemove(id,cf.pre_)
	assert(id)

	-- Get the most recent version of the node
	local cmd = dbc:prepare(self.queries.GET_NODE_, id)

	-- Run the query to get the node
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")
	cur:close()
	if not row then
		return nil
	end
	return maptoraw(cf,row)
end

function build_save_sql(dbc,cf,id,ik,ts,data)
	local flst,fmap=cf.flst,cf.fmap
	local ii,v2=1,{}
	if cf.pid then
		ii=ii+1
		local parent,rest=string_match(id, "^(.+)/(.-)$")
		parent=parent or ''
		table.insert(v2,dbc:prepare_val(parent))
	end
	if cf.ik then
		ii=ii+1
		table.insert(v2,ik or 0)
	end
	if cf.ts then
		ii=ii+1
		table.insert(v2,ts or os_time())
	end
	if fmap then
		for i=ii,#flst do
			local fk=flst[i]
			table.insert(v2,dbc:prepare_val(data[fmap[fk]]))
		end
	else
		for i=ii,#flst do
			table.insert(v2,dbc:prepare_val(data[flst[i]]))
		end
	end
	return cf.sfl,table.concat(v2,',')
end

function sqltb:save_node(id, data, ik, ts)
	local dbc,cf=self.dbc,self.cf
	local id=PreRemove(id,cf.pre_)
	assert(id)
	
	local fk,fv=build_save_sql(dbc,cf,id,ik,ts,data)
	-- Store the new node in the 'nodes' table
	local cmd = string_format(self.queries.INSERT_NODE,fk,dbc:prepare_val(id),fv)
	--print(cmd)
	local cur,err = assert(dbc:execute(cmd))
end

local function __get_cmd_wsl(self,ty,where,sort,limit)
	local cmd
	if where and #where==0 then where=nil end
	if sort and #sort==0 then sort=nil end
	if type(limit)=='string' and #limit==0 then limit=nil end
	if where then
		if sort then
			if limit then
				cmd=string_format(self.queries[ty..'_w_s_l'],_getdbwherefun(where,self._whereenv),string.gsub(sort,'%-',' desc'),limit)
			else
				cmd=string_format(self.queries[ty..'_w_s'],_getdbwherefun(where,self._whereenv),string.gsub(sort,'%-',' desc'))
			end
		else
			if limit then
				cmd=string_format(self.queries[ty..'_w_l'],_getdbwherefun(where,self._whereenv),limit)
			else
				cmd=string_format(self.queries[ty..'_w'],_getdbwherefun(where,self._whereenv))
			end
		end
	else
		if sort then
			if limit then
				cmd=string_format(self.queries[ty..'_s_l'],string.gsub(sort,'%-',' desc'),limit)
			else
				cmd=string_format(self.queries[ty..'_s'],string.gsub(sort,'%-',' desc'))
			end
		else
			if limit then
				cmd=string_format(self.queries[ty..'_l'],limit)
			else
				return nil
			end
		end
	end
	return cmd
end
function sqltb:get_child_nodes(parentid)
	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	local nodes = {}
	local cmd
	if cf.pid then
		cmd = dbc:prepare(self.queries.GET_CHILDNODES,parentid)
	elseif parentid=='' then
		cmd = dbc:prepare(self.queries.GET_CHILDNODES,parentid)
	else
		return nodes
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[cf.pre_..row[cf.id]] = maptoraw(cf,row)
		row = cur:fetch({}, "a")
	end

	cur:close()
	return nodes
end
local function autolstgen(a)
	local lst={}
	for k,v in ipairs(a) do lst[#lst+1]=k end
	return a,lst
end
function sqltb:get_child_nodes_ex(parentid,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_CHILDNODES',where,sort,limit)
	if not cmd then return autolstgen(self:get_child_nodes(parentid)) end

	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	local nodes,lst = {},{}
	if not cf.pid and #parentid>0 then return nodes,lst end

	if cf.pid then
		cmd=dbc:prepare(cmd,parentid)
	else
		cmd=dbc:prepare(cmd)
	end
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		local _i=cf.pre_..row[cf.id]
		nodes[_i] = maptoraw(cf,row)
		lst[#lst+1]=_i
		row = cur:fetch({}, "a")
	end

	cur:close()
	return nodes,lst
end

function sqltb:get_nodes_by_prefix(prefix)
	local dbc,cf=self.dbc,self.cf
	local nodes = {}
	
	local cmd
	if prefix then
		if prefix==cf.pre then
			prefix=''
		else
			prefix=assert(PreRemove(prefix,cf.pre_))
		end
		
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXNODES, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_NODES)
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[cf.pre_..row[cf.id]] = maptoraw(cf,row)
		row = cur:fetch({}, "a")
	end

	cur:close()
	return nodes
end
function sqltb:get_nodes_by_prefix_ex(prefix,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_PREFIXNODES',where,sort,limit)
	if not cmd then return autolstgen(self:get_nodes_by_prefix(prefix)) end

	local dbc,cf=self.dbc,self.cf
	if prefix==cf.pre or not prefix then
		prefix=''
	else
		prefix=assert(PreRemove(prefix,cf.pre_))
	end
	prefix = prefix .. "%"
	local nodes,lst = {},{}

	cmd=dbc:prepare(cmd,prefix)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		local _i=cf.pre_..row[cf.id]
		nodes[_i] = maptoraw(cf,row)
		lst[#lst+1]=_i
		row = cur:fetch({}, "a")
	end

	cur:close()
	return nodes,lst
end
function sqltb:get_ids_by_prefix(prefix)
	local dbc,cf=self.dbc,self.cf
	local nodes = {}
	local cmd
	if prefix then
		if prefix==cf.pre then
			prefix=''
		else
			prefix=assert(PreRemove(prefix,cf.pre_))
		end
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXIDS, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_IDS)
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[#nodes+1] = cf.pre_..row[cf.id]
		row = cur:fetch(row, "a")
	end

	cur:close()
	return nodes
end
function sqltb:get_ids_by_prefix_ex(prefix,where,sort,limit)
	local dbc,cf=self.dbc,self.cf
	
	local cmd=__get_cmd_wsl(self,'GET_PREFIXIDS',where,sort,limit)
	if not cmd then return self:get_ids_by_prefix(prefix) end
	
	if prefix==cf.pre or not prefix then
		prefix=''
	else
		prefix=assert(PreRemove(prefix,cf.pre_))
	end
	prefix = prefix .. "%"
	
	cmd=dbc:prepare(cmd,prefix)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[#nodes+1] = cf.pre_..row[cf.id]
		row = cur:fetch(row, "a")
	end

	cur:close()
	return nodes
end

function sqltb:get_child_ids(parentid)
	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	local nodes = {}
	local cmd
	if cf.pid then
		cmd = dbc:prepare(self.queries.GET_CHILDIDS,parentid)
	elseif parentid=='' then
		cmd = dbc:prepare(self.queries.GET_CHILDIDS,parentid)
	else
		return nodes
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[#nodes+1] = cf.pre_..row[cf.id]
		row = cur:fetch(row, "a")
	end

	cur:close()
	return nodes
end

function sqltb:get_child_ids_ex(parentid,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_CHILDIDS',where,sort,limit)
	if not cmd then return self:get_child_ids(parentid) end

	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	local nodes = {}
	if not cf.pid and #parentid>0 then return nodes end

	if cf.pid then
		cmd=dbc:prepare(cmd,parentid)
	else
		cmd=dbc:prepare(cmd)
	end
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur:fetch({}, "a")

	while row do
		nodes[#nodes+1] = cf.pre_..row[cf.id]
		row = cur:fetch(row, "a")
	end

	cur:close()
	return nodes
end

function sqltb:get_cnt_by_prefix(prefix)
	local dbc,cf=self.dbc,self.cf
	local cmd
	if prefix then
		if prefix==cf.pre then
			prefix=''
		else
			prefix=assert(PreRemove(prefix,cf.pre_))
		end
		prefix = prefix .. "%"
		cmd = dbc:prepare(self.queries.GET_PREFIXCNT, prefix)
	else
		cmd = dbc:prepare(self.queries.GET_CNT)
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur_fetch(cur,{}, "n")
	local cnt=row[1]
	cur:close()
	return tonumber(cnt)
end
function sqltb:get_cnt_by_prefix_ex(prefix,where,sort,limit)
	local dbc,cf=self.dbc,self.cf
	
	local cmd=__get_cmd_wsl(self,'GET_PREFIXCNT',where,sort,limit)
	if not cmd then return self:get_ids_by_prefix(prefix) end
	
	if prefix==cf.pre or not prefix then
		prefix=''
	else
		prefix=assert(PreRemove(prefix,cf.pre_))
	end
	prefix = prefix .. "%"
	
	cmd=dbc:prepare(cmd,prefix)
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur_fetch(cur,{}, "n")
	local cnt=row[1]
	cur:close()
	return tonumber(cnt)
end

function sqltb:get_child_cnt(parentid)
	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	local cmd
	if cf.pid then
		cmd = dbc:prepare(self.queries.GET_CHILDCNT,parentid)
	elseif parentid=='' then
		cmd = dbc:prepare(self.queries.GET_CHILDCNT,parentid)
	else
		return 0
	end

	local cur = assert(dbc:execute(cmd))
	local row = cur_fetch(cur,{}, "n")
	local cnt=row[1]
	cur:close()
	return tonumber(cnt)
end

function sqltb:get_child_cnt_ex(parentid,where,sort,limit)
	local cmd=__get_cmd_wsl(self,'GET_CHILDCNT',where,sort,limit)
	if not cmd then return self:get_child_cnt(parentid) end

	local dbc,cf=self.dbc,self.cf
	if parentid==cf.pre then
		parentid=''
	else
		parentid=assert(PreRemove(parentid,cf.pre_))
	end
	
	if not cf.pid and #parentid>0 then return 0 end

	if cf.pid then
		cmd=dbc:prepare(cmd,parentid)
	else
		cmd=dbc:prepare(cmd)
	end
	--print(cmd)
	local cur = assert(dbc:execute(cmd))
	local row = cur_fetch(cur,{}, "n")
	local cnt=row[1]
	cur:close()
	return tonumber(cnt)
end
function sqltb:del_node(id,deep)
	local dbc,cf=self.dbc,self.cf
	local id=PreRemove(id,cf.pre_)

	local cmd = dbc:prepare(self.queries.REMOVE_NODE, id)
	local cur = assert(dbc:execute(cmd))
	if deep and cf.pid then
		cmd = self:prepare(self.queries.REMOVE_SUBNODE, id..'/%')
		cur = assert(self:execute(cmd))
	end
end

function sqltb:del_sub_nodes(parentid)
	local dbc,cf=self.dbc,self.cf
	if IsRoot(parentid,cf.pre) then
		parentid=''
	else
		parentid=PreRemove(parentid,cf.pre_)
	end
	if parentid=='' then
		cmd = dbc:prepare(self.queries.REMOVE_SUBNODE, '%')
	else
		cmd = dbc:prepare(self.queries.REMOVE_SUBNODE, parentid..'/%')
	end
	cur = assert(dbc:execute(cmd))
end

